using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;

namespace DAO
{
    /// <summary> 
    ///Author: daiduong19051986@gmail.com
    /// <summary>
    public class TVC_MVPDataAccess : Connection
    {
        #region Local Variable
        private string _strTableName = "[TVC_MVPPRV]";
        private string _strSPGetTransferOutName = "dbo.[procTVC_MVPPRV_gettransferout]";
        #endregion Local Variable

        #region Method
        public TVC_MVPInfo Get(string db, String Code, ref string sErr)
        {
            string sql = @"SELECT   Code, Lookup, Mark, Description, From_, To_, ItemCode, Status, Amount, POSOContract, Supplier, WellCode, Department, Location, Account, Description1, Purpose, Frequency, Reference, 
                         DeliveryPoint, ReceivedIssued, Template, Prefix, Suppend, headShipDate, headShipBy, headBL, headPI, headFrom, headTo, headPO, headSupplier, lblSupplier, headDepartment, headWell, headDelivery, 
                         Budget, BudgetAccount, HeadBudget, HeadBudgetAccount
            FROM            TVC_MVP" + db + @"
            WHERE        (Code = @Code)";
            TVC_MVPInfo objEntr = new TVC_MVPInfo();
            InitConnect();
            InitCommand(sql);
            AddParameter(TVC_MVPInfo.Field.Code.ToString(), Code);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }

            if (list.Rows.Count > 0)
                objEntr = new TVC_MVPInfo(list.Rows[0]);
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            if (sErr != "") ErrorLog.SetLog(sErr);
            return objEntr;
        }

        public DataTable GetAll(string db, ref string sErr)
        {
            string sql = @"SELECT   Code, Lookup, Mark, Description, From_, To_, ItemCode, Status, Amount, POSOContract, Supplier, WellCode, Department, Location, Account, Description1, Purpose, Frequency, Reference, 
                         DeliveryPoint, ReceivedIssued, Template, Prefix, Suppend, headShipDate, headShipBy, headBL, headPI, headFrom, headTo, headPO, headSupplier, lblSupplier, headDepartment, headWell, headDelivery, 
                         Budget, BudgetAccount, HeadBudget, HeadBudgetAccount
            FROM            TVC_MVP" + db;
            InitConnect();
            InitCommand(sql);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        /// <summary>
        /// Return 1: Table is exist Identity Field
        /// Return 0: Table is not exist Identity Field
        /// Return -1: Erro
        /// </summary>
        /// <param name="tableName"></param>
        public Int32 Add(string db, TVC_MVPInfo objEntr, ref string sErr)
        {
            string sql = @"INSERT INTO TVC_MVP" + db + @"
                         (Code, Lookup, Mark, Description, From_, To_, ItemCode, Status, Amount, POSOContract, Supplier, WellCode, Department, Location, Account, Description1, Purpose, Frequency, Reference, DeliveryPoint, 
                         ReceivedIssued, Template, Prefix, Suppend, headShipDate, headShipBy, headBL, headPI, headFrom, headTo, headPO, headSupplier, lblSupplier, headDepartment, headWell, headDelivery,Budget, BudgetAccount, HeadBudget, HeadBudgetAccount)
            VALUES        (@Code,@Lookup,@Mark,@Description,@From_,@To_,@ItemCode,@Status,@Amount,@POSOContract,@Supplier,@WellCode,@Department,@Location,@Account,@Description1,@Purpose,@Frequency,@Reference,@DeliveryPoint,@ReceivedIssued,@Template,@Prefix,@Suppend,@headShipDate,@headShipBy,@headBL,@headPI,@headFrom,@headTo,@headPO,@headSupplier,@lblSupplier,@headDepartment,@headWell,@headDelivery, @Budget, @BudgetAccount, @HeadBudget, @HeadBudgetAccount)";
            int ret = -1;
            openConnection();
            InitCommand(sql);
            AddParameter(TVC_MVPInfo.Field.Code.ToString(), objEntr.Code);
            AddParameter(TVC_MVPInfo.Field.Lookup.ToString(), objEntr.Lookup);
            AddParameter(TVC_MVPInfo.Field.Mark.ToString(), objEntr.Mark);
            AddParameter(TVC_MVPInfo.Field.Description.ToString(), objEntr.Description);
            AddParameter(TVC_MVPInfo.Field.From_.ToString(), objEntr.From_);
            AddParameter(TVC_MVPInfo.Field.To_.ToString(), objEntr.To_);
            AddParameter(TVC_MVPInfo.Field.ItemCode.ToString(), objEntr.ItemCode);
            AddParameter(TVC_MVPInfo.Field.Status.ToString(), objEntr.Status);
            AddParameter(TVC_MVPInfo.Field.Amount.ToString(), objEntr.Amount);
            AddParameter(TVC_MVPInfo.Field.POSOContract.ToString(), objEntr.POSOContract);
            AddParameter(TVC_MVPInfo.Field.Supplier.ToString(), objEntr.Supplier);
            AddParameter(TVC_MVPInfo.Field.WellCode.ToString(), objEntr.WellCode);
            AddParameter(TVC_MVPInfo.Field.Department.ToString(), objEntr.Department);
            AddParameter(TVC_MVPInfo.Field.Location.ToString(), objEntr.Location);
            AddParameter(TVC_MVPInfo.Field.Account.ToString(), objEntr.Account);
            AddParameter(TVC_MVPInfo.Field.Description1.ToString(), objEntr.Description1);
            AddParameter(TVC_MVPInfo.Field.Purpose.ToString(), objEntr.Purpose);
            AddParameter(TVC_MVPInfo.Field.Frequency.ToString(), objEntr.Frequency);
            AddParameter(TVC_MVPInfo.Field.Reference.ToString(), objEntr.Reference);
            AddParameter(TVC_MVPInfo.Field.DeliveryPoint.ToString(), objEntr.DeliveryPoint);
            AddParameter(TVC_MVPInfo.Field.ReceivedIssued.ToString(), objEntr.ReceivedIssued);
            AddParameter(TVC_MVPInfo.Field.Template.ToString(), objEntr.Template);
            AddParameter(TVC_MVPInfo.Field.Prefix.ToString(), objEntr.Prefix);
            AddParameter(TVC_MVPInfo.Field.Suppend.ToString(), objEntr.Suppend);
            AddParameter(TVC_MVPInfo.Field.headShipDate.ToString(), objEntr.headShipDate);
            AddParameter(TVC_MVPInfo.Field.headShipBy.ToString(), objEntr.headShipBy);
            AddParameter(TVC_MVPInfo.Field.headBL.ToString(), objEntr.headBL);
            AddParameter(TVC_MVPInfo.Field.headPI.ToString(), objEntr.headPI);
            AddParameter(TVC_MVPInfo.Field.headFrom.ToString(), objEntr.headFrom);
            AddParameter(TVC_MVPInfo.Field.headTo.ToString(), objEntr.headTo);
            AddParameter(TVC_MVPInfo.Field.headPO.ToString(), objEntr.headPO);
            AddParameter(TVC_MVPInfo.Field.headSupplier.ToString(), objEntr.headSupplier);
            AddParameter(TVC_MVPInfo.Field.lblSupplier.ToString(), objEntr.lblSupplier);
            AddParameter(TVC_MVPInfo.Field.headDepartment.ToString(), objEntr.headDepartment);
            AddParameter(TVC_MVPInfo.Field.headWell.ToString(), objEntr.headWell);
            AddParameter(TVC_MVPInfo.Field.headDelivery.ToString(), objEntr.headDelivery);

            AddParameter(TVC_MVPInfo.Field.Budget.ToString(), objEntr.Budget);
            AddParameter(TVC_MVPInfo.Field.BudgetAccount.ToString(), objEntr.BudgetAccount);
            AddParameter(TVC_MVPInfo.Field.HeadBudget.ToString(), objEntr.HeadBudget);
            AddParameter(TVC_MVPInfo.Field.HeadBudgetAccount.ToString(), objEntr.HeadBudgetAccount);

            try
            {
                //command.ExecuteNonQuery();
                object tmp = executeScalar();
                if (tmp != null && tmp != DBNull.Value)
                    ret = Convert.ToInt32(tmp);
                else
                    ret = 0;
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }

        public string Update(string db, TVC_MVPInfo objEntr)
        {
            string sql = @"UPDATE       TVC_MVP" + db + @"
            SET                Lookup = @Lookup, Mark = @Mark, Description = @Description, From_ = @From_, To_ = @To_, ItemCode = @ItemCode, Status = @Status, Amount = @Amount, POSOContract = @POSOContract, 
                         Supplier = @Supplier, WellCode = @WellCode, Department = @Department, Location = @Location, Account = @Account, Description1 = @Description1, Purpose = @Purpose, Frequency = @Frequency, 
                         Reference = @Reference, DeliveryPoint = @DeliveryPoint, ReceivedIssued = @ReceivedIssued, Template = @Template, Prefix = @Prefix, Suppend = @Suppend, headShipDate = @headShipDate, 
                         headShipBy = @headShipBy, headBL = @headBL, headPI = @headPI, headFrom = @headFrom, headTo = @headTo, headPO = @headPO, headSupplier = @headSupplier, lblSupplier = @lblSupplier, 
                         headDepartment = @headDepartment, headWell = @headWell, headDelivery = @headDelivery, Budget = @Budget, BudgetAccount = @BudgetAccount, HeadBudget = @HeadBudget, HeadBudgetAccount = @HeadBudgetAccount
            WHERE        (Code = @Code)";
            openConnection();
            InitCommand(sql);
            AddParameter(TVC_MVPInfo.Field.Code.ToString(), objEntr.Code);
            AddParameter(TVC_MVPInfo.Field.Lookup.ToString(), objEntr.Lookup);
            AddParameter(TVC_MVPInfo.Field.Mark.ToString(), objEntr.Mark);
            AddParameter(TVC_MVPInfo.Field.Description.ToString(), objEntr.Description);
            AddParameter(TVC_MVPInfo.Field.From_.ToString(), objEntr.From_);
            AddParameter(TVC_MVPInfo.Field.To_.ToString(), objEntr.To_);
            AddParameter(TVC_MVPInfo.Field.ItemCode.ToString(), objEntr.ItemCode);
            AddParameter(TVC_MVPInfo.Field.Status.ToString(), objEntr.Status);
            AddParameter(TVC_MVPInfo.Field.Amount.ToString(), objEntr.Amount);
            AddParameter(TVC_MVPInfo.Field.POSOContract.ToString(), objEntr.POSOContract);
            AddParameter(TVC_MVPInfo.Field.Supplier.ToString(), objEntr.Supplier);
            AddParameter(TVC_MVPInfo.Field.WellCode.ToString(), objEntr.WellCode);
            AddParameter(TVC_MVPInfo.Field.Department.ToString(), objEntr.Department);
            AddParameter(TVC_MVPInfo.Field.Location.ToString(), objEntr.Location);
            AddParameter(TVC_MVPInfo.Field.Account.ToString(), objEntr.Account);
            AddParameter(TVC_MVPInfo.Field.Description1.ToString(), objEntr.Description1);
            AddParameter(TVC_MVPInfo.Field.Purpose.ToString(), objEntr.Purpose);
            AddParameter(TVC_MVPInfo.Field.Frequency.ToString(), objEntr.Frequency);
            AddParameter(TVC_MVPInfo.Field.Reference.ToString(), objEntr.Reference);
            AddParameter(TVC_MVPInfo.Field.DeliveryPoint.ToString(), objEntr.DeliveryPoint);
            AddParameter(TVC_MVPInfo.Field.ReceivedIssued.ToString(), objEntr.ReceivedIssued);
            AddParameter(TVC_MVPInfo.Field.Template.ToString(), objEntr.Template);
            AddParameter(TVC_MVPInfo.Field.Prefix.ToString(), objEntr.Prefix);
            AddParameter(TVC_MVPInfo.Field.Suppend.ToString(), objEntr.Suppend);
            AddParameter(TVC_MVPInfo.Field.headShipDate.ToString(), objEntr.headShipDate);
            AddParameter(TVC_MVPInfo.Field.headShipBy.ToString(), objEntr.headShipBy);
            AddParameter(TVC_MVPInfo.Field.headBL.ToString(), objEntr.headBL);
            AddParameter(TVC_MVPInfo.Field.headPI.ToString(), objEntr.headPI);
            AddParameter(TVC_MVPInfo.Field.headFrom.ToString(), objEntr.headFrom);
            AddParameter(TVC_MVPInfo.Field.headTo.ToString(), objEntr.headTo);
            AddParameter(TVC_MVPInfo.Field.headPO.ToString(), objEntr.headPO);
            AddParameter(TVC_MVPInfo.Field.headSupplier.ToString(), objEntr.headSupplier);
            AddParameter(TVC_MVPInfo.Field.lblSupplier.ToString(), objEntr.lblSupplier);
            AddParameter(TVC_MVPInfo.Field.headDepartment.ToString(), objEntr.headDepartment);
            AddParameter(TVC_MVPInfo.Field.headWell.ToString(), objEntr.headWell);
            AddParameter(TVC_MVPInfo.Field.headDelivery.ToString(), objEntr.headDelivery);

            AddParameter(TVC_MVPInfo.Field.Budget.ToString(), objEntr.Budget);
            AddParameter(TVC_MVPInfo.Field.BudgetAccount.ToString(), objEntr.BudgetAccount);
            AddParameter(TVC_MVPInfo.Field.HeadBudget.ToString(), objEntr.HeadBudget);
            AddParameter(TVC_MVPInfo.Field.HeadBudgetAccount.ToString(), objEntr.HeadBudgetAccount);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

        public string Delete(string db, String Code)
        {
            string sql = @"DELETE FROM TVC_MVP" + db + @"
            WHERE        (Code = @Code)";
            openConnection();
            InitCommand(sql);
            AddParameter(TVC_MVPInfo.Field.Code.ToString(), Code);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }



        public Boolean IsExist(string db, String Code)
        {
            string sql = @"SELECT        TOP (1) Code
            FROM            TVC_MVP" + db + @"
            WHERE        (Code = @Code)";
            InitConnect();
            InitCommand(sql);
            AddParameter(TVC_MVPInfo.Field.Code.ToString(), Code);

            string sErr = "";
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);
            if (list.Rows.Count == 1)
                return true;
            return false;
        }

        private string CreateWhereClause(TVC_MVPInfo obj)
        {
            String result = "";

            return result;
        }

        public DataTable Search(string columnName, string columnValue, string condition, string tableName, ref string sErr)
        {
            string query = "select * from " + _strTableName + " where " + columnName + " " + condition + " " + columnValue;
            DataTable list = new DataTable();
            openConnection();
            try
            {
                list = executeSelectQuery(query);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            //    if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        public DataTable GetTransferOut(string dtb, object from, object to, ref string sErr)
        {
            InitConnect();
            InitSPCommand(_strSPGetTransferOutName);
            AddParameter("DB", dtb);
            AddParameter("FROM", from);
            AddParameter("TO", to);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        #endregion Method

    }
}
